import datetime
import lib.mysql as mysql
import lib.config as config
import lib.util as util


# 出车数
def generateTaxiServiceTotal():
    sql = "select company.canton_id, taxi.company_id, DATE_FORMAT( driver_service_log.created_at, '%Y-%m-%d' ) AS date_format_daily, count( DISTINCT driver.taxi_id ) AS taxi_service_total from driver_service_log left join driver on driver_service_log.driver_id = driver.id left join taxi on driver.taxi_id = taxi.id left join company on taxi.company_id = company.id where driver.id IS NOT NULL AND driver_service_log.created_at between '{}' and '{}' group by company.canton_id, taxi.company_id, date_format_daily order by date_format_daily".format(start_time, end_time)
    return db.getRows(sql)


def getTaxiServiceUniqueColumn():
    sql = "select company.canton_id, taxi.company_id, DATE_FORMAT( driver_service_log.created_at, '%Y-%m-%d' ) AS date_format_daily from driver_service_log left join driver on driver_service_log.driver_id = driver.id left join taxi on driver.taxi_id = taxi.id left join company on taxi.company_id = company.id where driver.id IS NOT NULL AND driver_service_log.created_at between '{}' and '{}' group by company.canton_id, taxi.company_id, date_format_daily order by date_format_daily, company.canton_id, taxi.company_id".format(start_time, end_time)
    return db.getRows(sql)


def generateTaxiAll(end_time, canton_ids, company_ids):
    sql = "select taxi.company_id, company.canton_id, COUNT(*) as taxi_total from taxi left join company on taxi.company_id = company.id where company.canton_id in ({}) and taxi.company_id in ({}) and taxi.is_scrapped = 0 and taxi.created_at < '{}' group by company.canton_id, taxi.company_id".format(','.join(map(str, canton_ids)), ','.join(map(str, company_ids)), end_time)
    return db.getRows(sql)


def generateTaxiAdded(canton_ids, company_ids):
    sql = "select taxi.company_id, company.canton_id, DATE_FORMAT( taxi.created_at, '%Y-%m-%d' ) AS date_format_daily, COUNT(*) as taxi_added from taxi left join company on taxi.company_id = company.id where company.canton_id in ({}) and taxi.company_id in ({}) and taxi.is_scrapped = 0 and taxi.created_at between '{}' and '{}' group by company.canton_id, taxi.company_id, date_format_daily order by date_format_daily, company.canton_id, taxi.company_id".format(','.join(map(str, canton_ids)), ','.join(map(str, company_ids)), start_time, end_time)
    return db.getRows(sql)


# 注册车辆数
def generateTaxiTotal():
    driverServiceUniqueColumn = getTaxiServiceUniqueColumn()
    taxiTotalData = []
    if len(driverServiceUniqueColumn) == 0:
        return taxiTotalData
    serviceCantonIds = list(set([a[0] for a in driverServiceUniqueColumn]))
    serviceCompanyIds = list(set([a[1] for a in driverServiceUniqueColumn]))
    taxiTotalData = generateTaxiAll(start_time, serviceCantonIds, serviceCompanyIds)
    if not util.check_is_same_day(start_time, end_time):
        taxiAddedData = generateTaxiAdded(serviceCantonIds, serviceCompanyIds)
        taxiAddedTotalData = []
        taxiTotalDataMap = {f"{d[1]}-{d[0]}":d[2] for d in taxiTotalData}
        driverServiceUniqueColumn = [{'canton_id': d[0], 'company_id': d[1], 'date_format_daily': d[2], 'taxi_total': taxiTotalDataMap.get(f"{d[0]}-{d[1]}", 0)} for d in driverServiceUniqueColumn]
        _temp = {}
        taxiAddedDataMap = {f"{d[1]}-{d[0]}-{d[2]}":d[3] for d in taxiAddedData}
        for item in driverServiceUniqueColumn:
            _data = item
            if f"{item['canton_id']}+{item['company_id']}" not in _temp:
                _temp[f"{item['canton_id']}+{item['company_id']}"] = item['taxi_total']
            key = f"{item['canton_id']}-{item['company_id']}-{item['date_format_daily']}"
            if key in taxiAddedDataMap:
                _data['taxi_total'] = _temp[f"{item['canton_id']}+{item['company_id']}"] + taxiAddedDataMap[key]
                _temp[f"{item['canton_id']}+{item['company_id']}"] = _data['taxi_total']
            else:
                _data['taxi_total'] = _temp[f"{item['canton_id']}+{item['company_id']}"]
            taxiAddedTotalData.append(_data)
        return taxiAddedTotalData
    return [{'canton_id': d[1], 'company_id': d[0], 'date_format_daily': start_time, 'taxi_total': d[2]} for d in taxiTotalData]


def getMainData():
    taxiServiceTotalData = generateTaxiServiceTotal()
    if len(taxiServiceTotalData) == 0:
        return
    taxiTotalData = generateTaxiTotal()
    taxiTotalDataMap = {f"{d['canton_id']}-{d['company_id']}-{d['date_format_daily']}": d['taxi_total'] for d in taxiTotalData}
    insert_data = [{'canton_id': d[0], 'company_id': d[1], 'stats_date': d[2], 'taxi_service_total': d[3], 'taxi_total': taxiTotalDataMap.get(f"{d[0]}-{d[1]}-{d[2]}", 0)} for d in taxiServiceTotalData]
    return insert_data


start_time = util.get_start_date()
end_time = util.get_end_date()

current_time = datetime.datetime.now()
if end_time is None:
    # 转为指定格式
    end_time = current_time.strftime("%Y-%m-%d 23:59:59")
else:
    end_time = f"{end_time} 23:59:59"

if start_time is None:
    start_time = datetime.datetime.strptime(end_time, "%Y-%m-%d %H:%M:%S") - datetime.timedelta(days=5)
    start_time = start_time.strftime("%Y-%m-%d")
current_time = current_time.strftime("%Y-%m-%d %H:%M:%S")


util.add_log(f"即将处理 {start_time} - {end_time} 的出车数据")
db = mysql.Db(config.config['mysql']['master'])
db1 = mysql.Db(config.config['mysql']['slave'])
call_data = getMainData()
call_size = len(call_data)
util.add_log(f"共有数据 {call_size}")
db1.cursor.execute("START TRANSACTION")
try:
    sql = "delete from dashboard_driver_service_daily where stats_date BETWEEN '{}' AND '{}'".format(start_time, end_time)
    db1.cursor.execute(sql)
    if call_size > 0:
        sql = "INSERT INTO `dashboard_driver_service_daily` (`canton_id`, `company_id`, `stats_date`, `taxi_service_total`, `taxi_total`, `created_at`, `updated_at`) VALUES "
        for item in call_data:
            sql += f"({item['canton_id']}, {item['company_id']}, '{item['stats_date']}', {item['taxi_service_total']}, {item['taxi_total']}, '{current_time}', '{current_time}'),"
        sql = sql.strip(',')
        db1.cursor.execute(sql)
    db1.conn.commit()
    util.add_log("处理完成")
except Exception as e:
    db1.conn.rollback()
    util.add_log(f"处理失败: {e}")
